package com.ys.otms.utils;

import com.ys.otms.dto.ShipInfoDto;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2018/10/15.
 */
public class excelUtil {
    private static Logger logger = LoggerFactory.getLogger(excelUtil.class);

    public static JSONObject readExcel(JSONObject obj) throws Exception {
        logger.info("ShipServiceImpl readExcel start obj{}",obj);
        String fileName = JsonUtil.getJSONObjectString(obj,"fileName");

        JSONObject jsonObject = new JSONObject();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
                .substring(fileName.lastIndexOf(".") + 1);
        Workbook wb = null;
        if ("xlsx".equals(extension)) {
            wb= new XSSFWorkbook(new FileInputStream(new File(fileName)));
        } else {
            jsonObject.put("SUCCESS",false);
            jsonObject.put("MSG","不支持的文件类型");
            return jsonObject;
        }
        Sheet sheet = wb.getSheetAt(0);
        Object valueCell = null;
        Row row = null;
        Cell cell = null;
        int countRow =0;
        int countCell =0;
        List<ShipInfoDto> list = new ArrayList<ShipInfoDto>();
        for (int i = sheet.getFirstRowNum(); i<=sheet.getLastRowNum(); i++) {
            if(i==0){
                //跳过第一行
                continue;
            }
            row = sheet.getRow(i);
            if (row == null) {
                countRow++;
                continue;
            }
            ShipInfoDto shipInfo = new ShipInfoDto();
            cell = row.getCell(0);
            if(cell == null){
                countCell++;
                continue;
            }else{
                if("".equals(valueType(cell))){
                    countCell++;
                    continue;
                }
            }
            shipInfo.setShipName(valueType(cell).toString());

            cell = row.getCell(1);
            if(cell == null){
                countCell++;
                continue;
            }else{
                if("".equals(valueType(cell))){
                    countCell++;
                    continue;
                }
            }
            shipInfo.setTonnage(Double.parseDouble(valueType(cell).toString()));

            cell = row.getCell(2);
            if(cell == null){
                countCell++;
                continue;
            }else{
                if("".equals(valueType(cell))){
                    countCell++;
                    continue;
                }
            }
            shipInfo.setPhoneNum(valueType(cell).toString());
//            cell = row.getCell(3);
//            if(cell == null){
//                countCell++;
//                continue;
//            }else{
//                if("".equals(valueType(cell))){
//                    countCell++;
//                    continue;
//                }
//            }
//            shipInfo.setHatch(Integer.parseInt(valueType(cell).toString()));
//
//            cell = row.getCell(4);
//
//            if(cell == null){
//                countCell++;
//                continue;
//            }else{
//                if("".equals(valueType(cell))){
//                    countCell++;
//                    continue;
//                }
//            }
//            shipInfo.setShipTypeName(valueType(cell).toString());
//
//            cell = row.getCell(5);
//            if(cell == null){
//                countCell++;
//                continue;
//            }else{
//                if("".equals(valueType(cell))){
//                    countCell++;
//                    continue;
//                }
//            }
    //        shipInfo.setGoodsName(valueType(cell).toString());
            list.add(shipInfo);
        }
        jsonObject.put("shipLists",list);
        jsonObject.put("countRow",countRow +countCell);

        return jsonObject;
    }

    private static Object valueType(Cell cell){
        Object value = null;
        DecimalFormat df = new DecimalFormat("0");// 格式化 number String
        // 字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
        DecimalFormat nf = new DecimalFormat("0");// 格式化数字
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                // System.out.println(i + "行" + j + " 列 is String type");
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if ("General".equals(cell.getCellStyle()
                        .getDataFormatString())) {
                    value = nf.format(cell.getNumericCellValue());
                } else {
                    value = sdf.format(HSSFDateUtil.getJavaDate(cell
                            .getNumericCellValue()));
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_BLANK://空格，空白
                value = "";
                break;
            default:
                value = cell.toString();
        }
        return value;
    }

}
